﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace QD.Snapshot.Core.DAL
{
    public class Snap : DAO
    {
        public static int Add(Model.Snap model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into sql_s003.dbo.vIFO_Snap_m(");
            strSql.Append("ItemID,UserID,Keyword,Ranking,CopyForm,Page,platform,ClientId,updatetime,index_key,HTML)");
            strSql.Append(" values (");
            strSql.Append("@ItemID,@UserID,@Keyword,@Ranking,@CopyForm,@Page,@platform,@ClientId,@updatetime,@index_key,@HTML)");
            SqlParameter[] parameters = {
					new SqlParameter("@ItemID", model.itemid),
					new SqlParameter("@UserID", model.userid),
					new SqlParameter("@Keyword", model.keyword),
					new SqlParameter("@Ranking", model.ranking),
					new SqlParameter("@CopyForm", model.copyform),
					new SqlParameter("@Page", model.page),
					new SqlParameter("@platform",  model.platform),
					new SqlParameter("@ClientId", model.clientid),
                    new SqlParameter("@updatetime", model.updatetime),
                    new SqlParameter("@index_key", model.index_key),
					new SqlParameter("@HTML", model.html)};

            return SqlHelper2013.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        public static int UpdateBySearch(Model.Snap model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update sql_s003.dbo.vIFO_Snap_m set ");
            strSql.Append("ItemID=@ItemID,");
            strSql.Append("UserID=@UserID,");
            strSql.Append("Keyword=@Keyword,");
            strSql.Append("Ranking=@Ranking,");
            strSql.Append("CopyForm=@CopyForm,");
            strSql.Append("platform=@platform,");
            strSql.Append("state=@state,");
            strSql.Append("ClientId=@ClientId,");
            strSql.Append("updatetime=@updatetime,");
            strSql.Append("index_key=@index_key,");
            strSql.Append("Page=@Page,");
            strSql.Append("HTML=@HTML");
            strSql.Append(" where ID=@ID");

            SqlParameter[] parameters = {
                    new SqlParameter("@ID", model.id),
					new SqlParameter("@ItemID", model.itemid),
					new SqlParameter("@UserID", model.userid),
					new SqlParameter("@Keyword", model.keyword),
					new SqlParameter("@Ranking", model.ranking),
					new SqlParameter("@CopyForm", model.copyform),
                    new SqlParameter("@state", model.state),
					new SqlParameter("@Page", model.page),
					new SqlParameter("@platform",  model.platform),
					new SqlParameter("@ClientId", model.clientid),
                    new SqlParameter("@updatetime", model.updatetime),
                    new SqlParameter("@index_key", model.index_key),
					new SqlParameter("@HTML",model.html)};
            return SqlHelper2013.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新生成快照时间
        /// </summary>
        public static int UpdateByMack(Model.Snap model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update sql_s003.dbo.vIFO_Snap_m set ");
            strSql.Append("state=@state,");
            strSql.Append("picurl=@picurl,");
            strSql.Append("HTML=@HTML");
            strSql.Append(" where ID=@ID");

            SqlParameter[] parameters = {
                    new SqlParameter("@id", model.id),
                    new SqlParameter("@state", model.state),
                    new SqlParameter("@picurl", model.picurl),
					new SqlParameter("@HTML",model.html)};
            return SqlHelper2013.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        public static int GetCountByMack(int userid = 0)
        {
            string sql = "select count(1) from sql_s003.dbo.vifo_snap_m where state=0";
            if (userid > 0)
                sql = string.Format("select count(1) from sql_s003.dbo.vifo_snap_m where state=0 and userid={0}", userid);
            object obj = SqlHelper2013.ExecuteScalar(CommandType.Text, sql);
            if (obj != null)
                return Convert.ToInt32(obj);
            return 0;
        }

        public static Model.Snap GetExists(string keyword, int userid, int copyform, int ranking, string index_key, string platform)
        {
            List<Model.Snap> list = GetList(keyword, userid, copyform);
            if (list == null) return null;

            int index = -1;
            for (int i = 0; i < list.Count; i++)
            {
                //上一次的行索引
                if (list[i].ranking == ranking)
                    index = i;

                //上词平台
                if ((!string.IsNullOrEmpty(platform)) && list[i].platform.Equals(platform))
                {
                    index = i;
                }

                //校验上一次上词第几行的标识(标识优先级最高)
                if (list[i].index_key.Equals(index_key))
                {
                    index = i;
                    break;
                }
            }

            if (index > -1 && list.Count > 0)
                return list[index];
            else
                return null;
        }

        public static List<Model.Snap> GetList(string keyword, int userid, int copyform)
        {
            string sql = "select top 10 id,userid,ranking,index_key,platform from sql_s003.dbo.vifo_snap_m with(nolock) where userid=@userid and keyword=@keyword and copyform=@copyform";
            SqlParameter[] parameters = {
					new SqlParameter("@keyword",keyword),
					new SqlParameter("@userid", userid),
					new SqlParameter("@copyform", copyform)};

            List<Model.Snap> list = null;
            using (SqlDataReader dr = SqlHelper2013.ExecuteReader(CommandType.Text, sql, parameters))
            {
                if (dr != null)
                {
                    if (dr.HasRows)
                        list = new List<Model.Snap>();
                    while (dr.Read())
                    {
                        Model.Snap model = new Model.Snap();
                        model.id = Convert.ToInt32(dr["id"]);
                        model.ranking = Convert.ToInt32(dr["ranking"]);
                        model.userid = Convert.ToInt32(dr["userid"]);
                        model.index_key = Convert.ToString(dr["index_key"]);
                        model.platform = Convert.ToString(dr["platform"]);
                        list.Add(model);
                    }
                    dr.Close();
                }
            }

            return list;

        }
    
        public static List<int> GetListIdByMack(int pageSize = 10, int userid = 0)
        {
            List<int> list = null;
            string sql = string.Format("select top {0} id from sql_s003.dbo.vifo_snap_m where state=0", pageSize);
            if (userid > 0)
                sql = string.Format("select top {0} id from sql_s003.dbo.vifo_snap_m where state=0 and userid={1}", pageSize, userid);
            try
            {
                using (SqlDataReader dr = SqlHelper2013.ExecuteReader(CommandType.Text, sql, null))
                {
                    if (dr != null)
                    {
                        if (dr.HasRows)
                            list = new List<int>();
                        while (dr.Read())
                        {
                            list.Add(Convert.ToInt32(dr["id"]));
                        }
                        dr.Close();
                    }
                }
            }
            catch (Exception e) { }
            return list;
        }

        public static Model.Snap Get(int id)
        {
            Model.Snap model = null;
            string sql = string.Format("select id,itemid,userid,keyword,copyform,ranking,page,platform,index_key,updatetime,html from sql_s003.dbo.vifo_snap_m where id={0}", id);
            using (SqlDataReader dr = SqlHelper2013.ExecuteReader(CommandType.Text, sql, null))
            {
                if (dr != null)
                {
                    if (dr.HasRows) model = new Model.Snap();
                    while (dr.Read())
                    {
                        model.id = Convert.ToInt32(dr["id"]);
                        model.itemid = Convert.ToInt32(dr["itemid"]);
                        model.userid = Convert.ToInt32(dr["userid"]);
                        model.keyword = Convert.ToString(dr["keyword"]);
                        model.copyform = Convert.ToInt32(dr["copyform"]);

                        model.ranking = Convert.ToInt32(dr["ranking"]);
                        model.page = Convert.ToInt32(dr["page"]);
                        model.platform = Convert.ToString(dr["platform"]);
                        model.index_key = Convert.ToString(dr["index_key"]);
                        model.updatetime = Convert.ToDateTime(dr["updatetime"]);
                        model.html = Convert.ToString(dr["html"]);
                    }
                    dr.Close();
                }
            }
            return model;
        }

    }
}